**** Set Globals
clear
global home "C:\Users\venator\Dropbox\" // REPLACE WITH LOCATION OF REPLICATION FOLDER
global data "${home}\icflm_replication_archive\Data\CCC"
global output  "${home}\icflm_replication_archive\Output\"


import excel "${data}/nationaldatabaseofchildcareprices.xlsx", clear firstrow
ren *, lower

tostring(county_fips_code), g(county_str)

g st_str = substr(county_str,1,2)
g st_str2 = substr(county_str,1,1)
g state_fip = st_str if county_fips_code >= 9999
replace state_fip = st_str2 if county_fips_code <=9999
destring(state_fip), replace

g hh_under6 =h_under6_bothwork+ h_under6_fwork +h_under6_mwork+ h_under6_singlem
**** Create County Level Measure, standardized for PSID child penalty analysis
qui summ mctoddler
g cc_county_std = (mctoddler -`r(mean)')/`r(sd)'
preserve
keep county_fips_code state_fip cc_county_std mctoddler studyyear hh_under6
save "${data}/county_NCDP_ccprice.dta",replace
restore

**** Create State Level Measure, standardized for ACS analysis
preserve
collapse (mean) mctoddler [fw=hh_under6], by (state_fip studyyear)
summ mctoddler
g cc_state_std =(mctoddler -`r(mean)')/`r(sd)'
keep state_fip cc_state_std mctoddler studyyear 
save "${data}/state_NCDP_ccprice.dta",replace
restore
